﻿namespace NyírásAdat
{
    using System;
    using System.Collections.Generic;
    using System.Collections.Specialized;
    using System.ComponentModel;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Dynamic;
    using System.Linq;
    using System.Text;
    using extensions;

    /// <summary>
    /// Dynamic wrapper for a database table.
    /// </summary>
    public class DynamicModel : DynamicObject
    {
        DbProviderFactory _factory;
        string _connectionString;
        public static DynamicModel Open(string connectionStringName)
        {
            dynamic dm = new DynamicModel(connectionStringName);
            return dm;
        }
        public DynamicModel(string connectionStringName, string tableName = "",
            string primaryKeyField = "", string descriptorField = "")
        {
            TableName = tableName == "" ? this.GetType().Name : tableName;
            PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
            DescriptorField = descriptorField;
            var _providerName = "System.Data.SqlClient";

            _factory = DbProviderFactories.GetFactory(_providerName);
            _connectionString = connectionStringName;
        }

        /// <summary>
        /// Creates ExpandoObject from post event - validated against Db columns.
        ///  </summary>
        public dynamic CreateFrom(NameValueCollection coll)
        {
            dynamic result = new ExpandoObject();
            var dc = (IDictionary<string, object>)result;
            var schema = Schema;
            //loop the collection, setting only what's in the Schema
            foreach (var item in coll.Keys)
            {
                var exists = schema.Any(x => x.COLUMN_NAME.ToLower() == item.ToString().ToLower());
                if (!exists) continue;
                var key = item.ToString();
                var val = coll[key];
                dc.Add(key, val);
            }
            return result;
        }

        /// <summary>
        /// Use this method to generate DataTable from collection of objects.
        /// </summary>
        /// <typeparam name="T">Type T</typeparam>
        /// <param name="data">List of Generic T</param>
        /// <returns>DataTable</returns>
        public virtual DataTable ConvertToDataTable<T>(IList<T> data)
        {
            var iTable = new DataTable();
            var properties = TypeDescriptor.GetProperties(typeof(T));

            foreach (PropertyDescriptor prop in properties)
            {
                iTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                foreach (T item in data)
                {
                    DataRow row = iTable.NewRow();
                    foreach (PropertyDescriptor iProp in properties)
                    {
                        row[iProp.Name] = prop.GetValue(item) ?? DBNull.Value;
                        iTable.Rows.Add(row);
                    }
                }
            }

            return iTable;
        }

        /// <summary>
        /// Use this method to generate DataTable from collection of string array
        /// </summary> 
        /// <param name="data">List of string array</param>
        /// <returns>DataTable</returns>
        public virtual DataTable ConvertToDataTable(List<string[]> data)
        {
            using (var table = new DataTable())
            {
                var col = data.Max(arr => arr.Length);
                for (var i = 0; i < col; i++)
                {
                    table.Columns.Add(i.ToString(), typeof(string));
                }

                foreach (var array in data.Where(array => array != null))
                {
                    table.Rows.Add(array);
                }
                return table;
            }
        }

        /// <summary>
        /// Returns the default value for a column.
        /// </summary>
        public dynamic DefaultValue(dynamic column)
        {
            dynamic result;
            string def = column.COLUMN_DEFAULT;
            if (string.IsNullOrEmpty(def))
            {
                result = null;
            }
            else if (def == "getdate()" || def == "(getdate())")
            {
                result = DateTime.Now.ToShortDateString();
            }
            else if (def == "newid()")
            {
                result = Guid.NewGuid().ToString();
            }
            else
            {
                result = def.Replace("(", "").Replace(")", "");
            }
            return result;
        }

        /// <summary>
        /// Creates an empty ExpandoObject set with column default values from schema.
        /// </summary>
        public dynamic Prototype
        {
            get
            {
                dynamic result = new ExpandoObject();
                var schema = Schema;
                foreach (dynamic column in schema)
                {
                    var dc = (IDictionary<string, object>)result;
                    dc.Add(column.COLUMN_NAME, DefaultValue(column));
                }

                result._Table = this;
                return result;
            }
        }

        public string DescriptorField { get; protected set; }


        IEnumerable<dynamic> _schema;
        public IEnumerable<dynamic> Schema => _schema ??
                                              (_schema = Query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0", TableName));
        /// <summary>
        /// Truncate given table.
        /// </summary> 
        public virtual int Truncate()
        {
            using (OpenConnection())
            {
                return Execute("TRUNCATE TABLE " + TableName);
            }
        }

        /// <summary>
        /// Enumerates DbCommand reader returning result.
        /// </summary>
        public virtual IEnumerable<dynamic> Query(string sql, params object[] args)
        {
            using (var conn = OpenConnection())
            {
                var rdr = CreateCommand(sql, conn, args).ExecuteReader();
                while (rdr.Read())
                {
                    yield return rdr.RecordToExpando();
                }
            }
        }
        public virtual IEnumerable<dynamic> Query(string sql, DbConnection connection, params object[] args)
        {
            using (var rdr = CreateCommand(sql, connection, args).ExecuteReader())
            {
                while (rdr.Read())
                {
                    yield return rdr.RecordToExpando();
                }
            }
        }

        /// <summary>
        /// Returns a single result.
        /// </summary>
        public virtual object Scalar(string sql, params object[] args)
        {
            object result;
            using (var conn = OpenConnection())
            {
                result = CreateCommand(sql, conn, args).ExecuteScalar();
            }
            return result;
        }
        /// <summary>
        /// Creates a DBCommand.
        /// </summary>
        DbCommand CreateCommand(string sql, DbConnection conn, params object[] args)
        {
            var result = _factory.CreateCommand();
            if (result != null)
            {
                result.Connection = conn;
                result.CommandText = sql;
                if (args.Length > 0)
                    result.AddParams(args);
            }
            return result;
        }
        /// <summary>
        /// Returns an Db OpenConnection.
        /// </summary>
        public virtual DbConnection OpenConnection()
        {
            var result = _factory.CreateConnection();
            if (result == null) return null;
            result.ConnectionString = _connectionString;
            result.Open();
            return result;
        }

        ///// <summary>
        ///// Builds a series of Upsert commands based on the object params. 
        ///// Objects with a primary key will be created at UPDATEs.
        ///// </summary> 
        /// <param name="t">Param objects may be POCO, Anonymous, NameValueCollection, or ExpandoObjects.</param>
        /// <returns>Collection of DbCommands</returns>
        public virtual List<DbCommand> BuildCommands(params object[] t)
        {
            var commands = new List<DbCommand>();
            foreach (var item in t)
            {
                if (HasPrimaryKey(item))
                {
                    commands.Add(CreateUpdateCommand(item.ToExpando(), GetPrimaryKey(item)));
                }
                else
                {
                    commands.Add(CreateInsertCommand(item.ToExpando()));
                }
            }
            return commands;
        }

        /// <summary>
        /// Executes DbCommand
        /// </summary>
        /// <param name="command">Sql statement</param>
        /// <returns>Db rows affected</returns>
        public virtual int Execute(DbCommand command)
        {
            return Execute(new DbCommand[] { command });
        }
        /// <summary>
        /// Builds and Executes DbCommand.
        /// </summary>
        /// <param name="sql">Sql statement</param>
        /// <param name="args">Sql params</param>
        /// <returns>Db rows affected</returns>
        public virtual int Execute(string sql, params object[] args)
        {
            return Execute(CreateCommand(sql, null, args));
        }

        /// <summary>
        /// Executes a series of DBCommands in a transaction
        /// </summary>
        /// <param name="commands">collection of Sql statements</param>
        /// <returns>Db rows affected</returns>
        public virtual int Execute(IEnumerable<DbCommand> commands)
        {
            var result = 0;
            using (var conn = OpenConnection())
            {
                using (var tx = conn.BeginTransaction())
                {
                    foreach (var cmd in commands)
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = tx;
                        result += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
            }
            return result;
        }
        public virtual string PrimaryKeyField { get; set; }
        /// <summary>
        /// Conventionally introspects the object passed in for a field that looks like a PK.
        /// </summary>
        public virtual bool HasPrimaryKey(object o)
        {
            return o.ToDictionary().ContainsKey(PrimaryKeyField);
        }
        /// <summary>
        /// If the object passed in has a property that matches the PrimaryKeyField it is returned here.
        /// </summary>
        public virtual object GetPrimaryKey(object o)
        {
            object result;
            o.ToDictionary().TryGetValue(PrimaryKeyField, out result);
            return result;
        }
        public virtual string TableName { get; set; }

        /// <summary>
        /// Returns collection of records that agree with provided params.
        /// </summary>
        /// <param name="where">Sql statement where clause</param>
        /// <param name="orderBy">Sql statement order by clause</param>
        /// <param name="limit">Sql statement limit clause</param>
        /// <param name="columns">Sql statement columns</param>
        /// <param name="args">Sql statement parameters</param>
        /// <returns></returns>
        public virtual IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args)
        {
            string sql = BuildSelect(where, orderBy, limit);
            return Query(string.Format(sql, columns, TableName), args);
        }
        private static string BuildSelect(string where, string orderBy, int limit)
        {
            string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1} " : "SELECT {0} FROM {1} ";
            if (!string.IsNullOrEmpty(where))
                sql += where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? where : " WHERE " + where;
            if (!String.IsNullOrEmpty(orderBy))
                sql += orderBy.Trim().StartsWith("order by", StringComparison.OrdinalIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
            return sql;
        }

        /// <summary>
        /// Returns a dynamic PagedResult.
        /// </summary>
        public virtual dynamic Paged(string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
        {
            return BuildPagedResult(where: where, orderBy: orderBy, columns: columns, pageSize: pageSize, currentPage: currentPage, args: args);
        }

        public virtual dynamic Paged(string sql, string primaryKey, string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
        {
            return BuildPagedResult(sql, primaryKey, where, orderBy, columns, pageSize, currentPage, args);
        }

        private dynamic BuildPagedResult(string sql = "", string primaryKeyField = "", string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
        {
            dynamic result = new ExpandoObject();
            string countSql;
            if (!string.IsNullOrEmpty(sql))
                countSql = $"SELECT COUNT({primaryKeyField}) FROM ({sql}) AS PagedTable";
            else
                countSql = $"SELECT COUNT({PrimaryKeyField}) FROM {TableName}";

            if (String.IsNullOrEmpty(orderBy))
            {
                orderBy = string.IsNullOrEmpty(primaryKeyField) ? PrimaryKeyField : primaryKeyField;
            }

            if (!string.IsNullOrEmpty(where))
            {
                if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
                {
                    where = " WHERE " + where;
                }
            }

            string query;
            if (!string.IsNullOrEmpty(sql))
                query =
                    $"SELECT {columns} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {orderBy}) AS Row, {columns} FROM ({sql}) AS PagedTable {@where}) AS Paged ";
            else
                query =
                    $"SELECT {columns} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {orderBy}) AS Row, {columns} FROM {TableName} {@where}) AS Paged ";

            var pageStart = (currentPage - 1) * pageSize;
            query += $" WHERE Row > {pageStart} AND Row <={(pageStart + pageSize)}";
            countSql += where;
            result.TotalRecords = Scalar(countSql, args);
            result.TotalPages = result.TotalRecords / pageSize;
            if (result.TotalRecords % pageSize > 0)
                result.TotalPages += 1;
            result.Items = Query(string.Format(query, columns, TableName), args);
            return result;
        }

        /// <summary>
        /// Returns a single row from the Db.
        /// </summary>
        public virtual dynamic Single(string where, params object[] args)
        {
            var sql = $"SELECT * FROM {TableName} WHERE {@where}";
            return Query(sql, args).FirstOrDefault();
        }
        /// <summary>
        /// Returns a single row from the Db.
        /// </summary>
        public virtual dynamic Single(object key, string columns = "*")
        {
            var sql = $"SELECT {columns} FROM {TableName} WHERE {PrimaryKeyField} = @0";
            return Query(sql, key).FirstOrDefault();
        }

        /// <summary>
        /// Returns a string/object dictionary
        /// </summary>
        public virtual IDictionary<string, object> KeyValues(string orderBy = "")
        {
            if (String.IsNullOrEmpty(DescriptorField))
                throw new InvalidOperationException("There's no DescriptorField set - do this in your constructor to describe the text value you want to see");
            var sql = $"SELECT {PrimaryKeyField},{DescriptorField} FROM {TableName} ";
            if (!String.IsNullOrEmpty(orderBy))
                sql += "ORDER BY " + orderBy;

            var results = Query(sql).ToList().Cast<IDictionary<string, object>>();
            return results.ToDictionary(key => key[PrimaryKeyField].ToString(), value => value[DescriptorField]);
        }

        /// <summary>
        /// Returns an ExpandoObject as a Dictionary
        /// </summary>
        public virtual IDictionary<string, object> ItemAsDictionary(ExpandoObject item)
        {
            return item;
        }
        /// <summary>
        /// Validate if Primary Key based on params.
        /// </summary>
        /// <param name="key">Key to lookup</param>
        /// <param name="item">ExpandoObject to search</param>
        /// <returns>true/false</returns> 
        public virtual bool ItemContainsKey(string key, ExpandoObject item)
        {
            var dc = ItemAsDictionary(item);
            return dc.ContainsKey(key);
        }

        /// <summary>
        /// Execute a collection of Upsert Sql commands within a transaction. 
        /// </summary>
        /// <param name="t">Object Array containing POCO, Anonymous, NameValueCollection, or ExpandoObjects</param>
        /// <returns>row count affected</returns>
        public virtual int Save(params object[] t)
        {
            foreach (var item in t)
            {
                if (!IsValid(item))
                {
                    throw new InvalidOperationException("Can't save this item: " + String.Join("; ", Errors.ToArray()));
                }
            }
            var commands = BuildCommands(t);
            return Execute(commands);
        }

        /// <summary>
        /// Converts a collection of ExpandoObjects to a datatable and then executes Sql BulkCopy command. 
        /// </summary>
        /// <param name="expando">Collection of ExpandoObjects</param>
        /// <param name="batch">BulkCopy batch count</param>
        /// <param name="timeoutInSeconds">BulkCopy Timeout in seconds</param>
        /// <returns>1 for success, 0 for fail</returns>
        public virtual int BulkCopy(List<dynamic> expando, int? batch, int? timeoutInSeconds)
        {
            var result = 0;
            batch = batch ?? 0;
            var iBatchSize = expando.Count < batch ? expando.Count : batch;

            var dataToInsert = ConvertToDataTable(expando);
            using (var conn = OpenConnection())
            {
                if (conn == null || !conn.State.Equals(ConnectionState.Open)) return result;
                using (var bulkCopy = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.Default))
                {
                    bulkCopy.DestinationTableName = "dbo." + TableName;
                    bulkCopy.BulkCopyTimeout = timeoutInSeconds ?? 500;
                    bulkCopy.BatchSize = iBatchSize.Value;
                    // Write from the source to the destination.
                    try
                    {
                        bulkCopy.WriteToServer(dataToInsert);
                        result = 1;
                    }
                    catch (Exception)
                    {
                        result = 0;
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Converts a collection of string array to a datatable and then executes Sql BulkCopy command. 
        /// </summary>
        /// <param name="expando">Collection of string array</param>
        /// <param name="batch">BulkCopy batch count</param>
        /// <param name="timeoutInSeconds">BulkCopy Timeout in seconds</param>
        /// <returns>1 for success, 0 for fail</returns>
        public virtual int BulkCopy(List<string[]> expando, int? batch, int? timeoutInSeconds)
        {
            var result = 0;
            batch = batch ?? 0;
            var iBatchSize = expando.Count < batch ? expando.Count : batch;

            using (var dataToInsert = ConvertToDataTable(expando))
            using (var conn = OpenConnection())
            {
                if (conn == null || !conn.State.Equals(ConnectionState.Open)) return result;
                using (var bulkCopy = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.Default))
                {
                    bulkCopy.DestinationTableName = "dbo." + TableName;
                    bulkCopy.BulkCopyTimeout = timeoutInSeconds ?? 500;
                    bulkCopy.BatchSize = iBatchSize.Value;
                    // Write from the source to the destination.
                    try
                    {
                        bulkCopy.WriteToServer(dataToInsert);
                        result = 1;
                    }
                    catch (Exception)
                    {
                        result = 0;
                        //throw;
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// Creates a SQL Insert command
        /// </summary>
        /// <param name="expando">ExpandoObject that is used to dynamically creates insert statement</param>
        /// <returns>Db command statement</returns>
        public virtual DbCommand CreateInsertCommand(dynamic expando)
        {
            var settings = (IDictionary<string, object>)expando;
            var sbKeys = new StringBuilder();
            var sbVals = new StringBuilder();
            var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
            var result = CreateCommand(stub, null);
            int counter = 0;
            foreach (var item in settings)
            {
                sbKeys.AppendFormat("{0},", "[" + item.Key + "]");
                sbVals.AppendFormat("@{0},", counter.ToString());
                result.AddParam(item.Value);
                counter++;
            }
            if (counter > 0)
            {
                var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
                var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
                var sql = string.Format(stub, TableName, keys, vals);
                result.CommandText = sql;
            }
            else throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
            return result;
        }

        /// <summary>
        /// Create Sql Update command
        /// </summary>
        /// <param name="expando">ExpandoObject that is used to dynamically creates Update statement</param>
        /// <param name="key">Primary Key</param>
        /// <returns>Db command</returns>
        public virtual DbCommand CreateUpdateCommand(dynamic expando, object key)
        {
            var settings = (IDictionary<string, object>)expando;
            var sbKeys = new StringBuilder();
            var stub = "UPDATE {0} SET {1} WHERE {2} = @{3}";
            var result = CreateCommand(stub, null);
            int counter = 0;
            foreach (var item in settings)
            {
                var val = item.Value;
                if (!item.Key.Equals(PrimaryKeyField, StringComparison.OrdinalIgnoreCase) && item.Value != null)
                {
                    result.AddParam(val);
                    sbKeys.AppendFormat("{0} = @{1}, \r\n", item.Key, counter.ToString());
                    counter++;
                }
            }
            if (counter > 0)
            {
                //add the key
                result.AddParam(key);
                //strip the last commas
                var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4);
                result.CommandText = string.Format(stub, TableName, keys, PrimaryKeyField, counter);
            }
            else throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
            return result;
        }

        /// <summary>
        /// Create Sql Update Command.
        /// </summary>
        /// <param name="expando">ExpandoObject that is used to dynamically creates Update statement</param>
        /// <param name="where">Sql Where statement</param>
        /// <param name="args">Object array to populate statement parameter</param>
        /// <returns></returns>
        public virtual DbCommand CreateUpdateWhereCommand(dynamic expando, string where = "", params object[] args)
        {
            var settings = (IDictionary<string, object>)expando;
            var sbKeys = new StringBuilder();
            string stub;

            if (!string.IsNullOrEmpty(where))
            {
                stub = where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? "UPDATE {0} SET {1} " : "UPDATE {0} SET {1} WHERE ";
                stub += where;
            }
            else
            {
                stub = "UPDATE {0} SET {1}";
            }


            var result = CreateCommand(stub, null, args);
            int counter = args.Length > 0 ? args.Length : 0;

            foreach (var item in settings)
            {
                var val = item.Value;
                if (!item.Key.Equals(PrimaryKeyField, StringComparison.OrdinalIgnoreCase) && item.Value != null)
                {
                    result.AddParam(val);
                    sbKeys.AppendFormat("{0} = @{1}, \r\n", item.Key, counter.ToString());
                    counter++;
                }
            }

            if (counter > 0)
            {
                //strip the last commas
                var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4);
                result.CommandText = string.Format(stub, TableName, keys);
            }
            else throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
            return result;
        }

        ///// <summary>
        ///// Removes one or more records from the DB according to the passed-in WHERE
        ///// </summary>
        /// <summary>
        /// Creates Sql Delete Command
        /// </summary>
        /// <param name="where">Sql Where statement</param>
        /// <param name="key">Db primary key</param>
        /// <param name="args">Object array to populate statement parameter</param>
        /// <returns>Db Command</returns>
        public virtual DbCommand CreateDeleteCommand(string where = "", object key = null, params object[] args)
        {
            var sql = $"DELETE FROM {TableName} ";
            if (key != null)
            {
                sql += $"WHERE {PrimaryKeyField}=@0";
                args = new[] { key };
            }
            else if (!string.IsNullOrEmpty(where))
            {
                sql += where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? where : "WHERE " + where;
            }
            return CreateCommand(sql, null, args);
        }

        public bool IsValid(dynamic item)
        {
            Errors.Clear();
            Validate(item);
            return Errors.Count == 0;
        }

        //Temporary holder for error messages
        public IList<string> Errors = new List<string>();

        /// <summary>
        /// Executes Sql Insert statement.
        /// </summary>
        /// <param name="o">object to be inserted into table</param>
        /// <returns>ExpandoObject</returns>
        public virtual dynamic Insert(object o)
        {
            var ex = o.ToExpando();
            if (!IsValid(ex))
            {
                throw new InvalidOperationException("Can't insert: " + String.Join("; ", Errors.ToArray()));
            }
            if (BeforeSave(ex))
            {
                using (dynamic conn = OpenConnection())
                {
                    var cmd = CreateInsertCommand(ex);
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT SCOPE_IDENTITY() as newID";
                    ex.ID = cmd.ExecuteScalar();
                    Inserted(ex);
                }
                return ex;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Executes Sql Update statement.
        /// </summary>
        /// <param name="o">object used to update table</param>
        /// <param name="key">Primary Key</param>
        /// <returns>Row count affected</returns> 
        public virtual int Update(object o, object key)
        {
            var ex = o.ToExpando();
            if (!IsValid(ex))
            {
                throw new InvalidOperationException("Can't Update: " + String.Join("; ", Errors.ToArray()));
            }
            var result = 0;
            if (BeforeSave(ex))
            {
                result = Execute(CreateUpdateCommand(ex, key));
                Updated(ex);
            }
            return result;
        }

        /// <summary>
        /// Executes Sql Update statement.
        /// </summary>
        /// <param name="o">object used to update table</param>
        /// <param name="where">Sql Where statement</param>
        /// <param name="args">Object array to populate statement parameter</param>
        /// <returns>
        /// 0 - No records updated, n - number of records updated
        /// </returns>
        public virtual int Update(object o, string where = "1=1", params object[] args)
        {
            if (string.IsNullOrWhiteSpace(where))
            {
                return 0;
            }

            var ex = o.ToExpando();
            if (!IsValid(ex))
            {
                throw new InvalidOperationException("Can't Update: " + String.Join("; ", Errors.ToArray()));
            }
            var result = 0;
            if (BeforeSave(ex))
            {
                result = Execute(CreateUpdateWhereCommand(ex, where, args));
                Updated(ex);
            }
            return result;
        }

        /// <summary>
        /// Executes Sql Delete statement.
        /// </summary>
        /// <param name="key">Primary Key</param>
        /// <param name="where">Sql Where statement</param>
        /// <param name="args">Object array to populate statement parameter</param>
        /// <returns></returns>
        public int Delete(object key = null, string where = "", params object[] args)
        {
            var deleted = Single(key);
            var result = 0;
            if (BeforeDelete(deleted))
            {
                result = Execute(CreateDeleteCommand(where: where, key: key, args: args));
                Deleted(deleted);
            }
            return result;
        }
        /// <summary>
        /// Sets default value of column.
        /// </summary>
        /// <param name="key">name of value to set</param>
        /// <param name="value">Value to set</param>
        /// <param name="item">Dynamic object to update named value</param>
        public void DefaultTo(string key, object value, dynamic item)
        {
            if (!ItemContainsKey(key, item))
            {
                var dc = (IDictionary<string, object>)item;
                dc[key] = value;
            }
        }

        public virtual void Validate(dynamic item) { }
        public virtual void Inserted(dynamic item) { }
        public virtual void Updated(dynamic item) { }
        public virtual void Deleted(dynamic item) { }
        public virtual bool BeforeDelete(dynamic item) { return true; }
        public virtual bool BeforeSave(dynamic item) { return true; }

        //Validation Methods
        public virtual void ValidatesPresenceOf(object value, string message = "Required")
        {
            if (string.IsNullOrEmpty(value?.ToString()))
                Errors.Add(message);
        }

        public virtual void ValidatesNumericalityOf(object value, string message = "Should be a number")
        {
            var type = value.GetType().Name;
            var numerics = new[] { "Int32", "Int16", "Int64", "Decimal", "Double", "Single", "Float" };
            if (!numerics.Contains(type))
            {
                Errors.Add(message);
            }
        }
        public virtual void ValidateIsCurrency(object value, string message = "Should be money")
        {
            if (value == null)
                Errors.Add(message);
            var val = decimal.MinValue;
            if (value != null) decimal.TryParse(value.ToString(), out val);
            if (val == decimal.MinValue)
                Errors.Add(message);
        }
        public int Count()
        {
            return Count(TableName);
        }

        public int Count(string where = "", params object[] args)
        {
            return (int)Scalar("SELECT COUNT(*) FROM dbo." + TableName + " " + where, args);
        }


        public int Count(string tableName, string where = "", params object[] args)
        {
            return (int)Scalar("SELECT COUNT(*) FROM " + tableName + " " + where, args);
        }
        public virtual bool Exists(string where = "", params object[] args)
        {
            var i = Count(where, args);
            return i >= 1;
        }
        /// <summary>
        /// Sql Query dynamic InvokeMemberBinder
        /// </summary>
        /// <param name="binder">InvokeMemberBinder</param>
        /// <param name="args">Object array to populate statement parameter</param>
        /// <param name="result">Out object result</param>
        /// <returns>true/false out result</returns>
        public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
        {
            var constraints = new List<string>();
            var counter = 0;
            var info = binder.CallInfo;
            if (info.ArgumentNames.Count != args.Length)
            {
                throw new InvalidOperationException("Please use named arguments for this type of query - the column name, orderby, columns, etc");
            }
            var op = binder.Name;
            var columns = " * ";
            string orderBy = $" ORDER BY {PrimaryKeyField}";
            string where = "";
            var whereArgs = new List<object>();

            if (info.ArgumentNames.Count > 0)
            {

                for (var i = 0; i < args.Length; i++)
                {
                    var name = info.ArgumentNames[i].ToLower();
                    switch (name)
                    {
                        case "orderby":
                            orderBy = " ORDER BY " + args[i];
                            break;
                        case "columns":
                            columns = args[i].ToString();
                            break;
                        default:
                            constraints.Add($" {name} = @{counter}");
                            whereArgs.Add(args[i]);
                            counter++;
                            break;
                    }
                }
            }

            if (constraints.Count > 0)
            {
                where = " WHERE " + string.Join(" AND ", constraints.ToArray());
            }
            switch (op.ToLower())
            {
                case "count":
                    result = Scalar("SELECT COUNT(*) FROM " + TableName + @where, whereArgs.ToArray());
                    break;
                case "sum":
                    result = Scalar("SELECT SUM(" + columns + ") FROM " + TableName + @where, whereArgs.ToArray());
                    break;
                case "max":
                    result = Scalar("SELECT MAX(" + columns + ") FROM " + TableName + @where, whereArgs.ToArray());
                    break;
                case "min":
                    result = Scalar("SELECT MIN(" + columns + ") FROM " + TableName + @where, whereArgs.ToArray());
                    break;
                case "avg":
                    result = Scalar("SELECT AVG(" + columns + ") FROM " + TableName + @where, whereArgs.ToArray());
                    break;
                default:

                    var sql = "SELECT TOP 1 " + columns + " FROM " + TableName + @where;
                    var justOne = op.StartsWith("First") || op.StartsWith("Last") || op.StartsWith("Get") || op.StartsWith("Single");

                    if (op.StartsWith("Last"))
                    {
                        orderBy = orderBy + " DESC ";
                    }
                    else
                    {
                        sql = "SELECT " + columns + " FROM " + TableName + @where;
                    }

                    result = justOne ? Query(sql + orderBy, whereArgs.ToArray()).FirstOrDefault() : Query(sql + orderBy, whereArgs.ToArray());
                    break;
            }
            return true;
        }
    }
}
